﻿#include "OiPCH.hpp"

namespace Oi
{
    namespace PLScannerCore
    {
        class DatabaseImpl : public Impl<Database>
        {
        public:
            void init();
            void exit();

        public:
            QSqlDatabase _db;
            QString _tb;
//            QString _tb_splicedata;
            QString _tb_fixed;
            QDateTime _lastTime;
        };

        void DatabaseImpl::init()
        {
            _tb = "plscanner_data";
            _tb_fixed = "plscanner_fixed_data";
            _db = QSqlDatabase::addDatabase("QSQLITE");
            _db.setDatabaseName(PLScanner::instance()->localDataDir);
            if (!_db.open())
            {
                OiWarning() << "plscanner.db open failed";
                return ;
            }
            QSqlQuery query(_db);
            _lastTime = QDateTime::currentDateTime();//系统当前时间
            QDateTime aweekAgo = _lastTime.addMonths(-2);//参数是可以为负数的，负数就是获取以前的时间，正数就是获取未来的时间

            QString deleteSql = QString("delete from plscanner.'%1' where datetime < '%2'").arg(_tb).arg(aweekAgo.toString("yyyy-MM-dd hh:mm:ss"));
            query.exec(deleteSql);
        }
        
        void DatabaseImpl::exit()
        {
            _db.close();
        }

        Database::Database()
        {
            OI_I(Database)->init();
        }

        Database::~Database()
        {
            OI_E(Database)->exit();
        }

        QString Database::tableName() const
        {
            return OI_IMPL()->_tb;
        }

        QSqlDatabase Database::sqlDatabase() const
        {
            return OI_IMPL()->_db;
        }

        QString Database::fixedTableName() const
        {
            return OI_IMPL()->_tb_fixed;
        }

        bool Database::open()
        {
            OI_F(Database);
            bool ret = d->_db.open();
            if (ret)
            {
                QSqlQuery query(d->_db);
                QString sql = QString("select count(*) from sqlite_master where type = 'table' and name='%1'").arg(d->_tb);

                query.exec(sql);

                if(query.next() && query.value(0).toInt() <= 0)
                {
                    sql = QString("CREATE TABLE %1 (id INTEGER PRIMARY KEY AUTOINCREMENT,"
                        "result INTEGER,"
                        "datetime TEXT NOT NULL,"
                        "formula TEXT,"
                        "user TEXT,"
                        "team INTEGER,"
                        "总宽度标准值 REAL,"
                        "P1宽度标准值 REAL,"
                        "边厚标准值 REAL,"
                        "中厚标准值 REAL,"
                        "总宽度 REAL,"
                        "P1宽度 REAL,"
                        "OP侧2P厚度 REAL,"
                        "OP侧肩部厚度 REAL,"
                        "中间厚度 REAL,"
                        "DR侧肩部厚度 REAL,"
                        "DR侧2P厚度 REAL)"
                       ).arg(d->_tb);

                    query.prepare(sql);
                    if(!query.exec())
                    {
                        OiWarning() << query.lastError();
                        OiWarning() << sql;
                        ret = false;
                    }
                    else
                    {
                        ret = true;
                    }
                }
                else
                {
                    ret = true;
                }
            }
            else
            {
                OiWarning() << "Database::"<< d->_db.lastError();
            }

            if (ret)
            {
                QSqlQuery query(d->_db);
                QString sql = QString("CREATE INDEX  DateIndex1 on %1 (datetime, formula)").arg(d->_tb);
                query.exec(sql);
            }

            if (ret)
            {
                QSqlQuery query(d->_db);
                QString sql = QString("select count(*) from sqlite_master where type = 'table' and name='%1'").arg(d->_tb_fixed);

                query.exec(sql);

                if (query.next() && query.value(0).toInt() <= 0)
                {
                    sql = QString("CREATE TABLE %1 (id INTEGER PRIMARY KEY AUTOINCREMENT,"
                        "result INTEGER,"
                        "datetime TEXT NOT NULL,"
                        "formula TEXT,"
                        "user TEXT,"
                        "team INTEGER,"
                        "厚度标准值 REAL,"
                        "厚度公差 REAL,"
                        "厚度 REAL)"
                    ).arg(d->_tb_fixed);

                    query.prepare(sql);
                    if (!query.exec())
                    {
                        OiWarning() << query.lastError();
                        OiWarning() << sql;
                        ret = false;
                    }
                    else
                    {
                        ret = true;
                    }
                }
                else
                {
                    ret = true;
                }
            }

            if (ret)
            {
                QSqlQuery query(d->_tb_fixed);
                QString sql = QString("CREATE INDEX  DateIndex2 on %1 (datetime, formula)").arg(d->_tb_fixed);
                query.exec(sql);
            }
            return ret;
        }

        bool Database::queryAll(QList<DataRow>& datas, const QDateTime from, const QDateTime end)
        {
            OI_F(Database);
            if (!d->_db.open())
            {
                return false;
            }

            QSqlQuery query(d->_db);
            QString strQuery = QString("SELECT * FROM %1 WHERE datetime BETWEEN '%2' AND '%3'")
                .arg(d->_tb)
                .arg(from.toString("yyyy-MM-dd hh:mm:ss"))
                .arg(end.toString("yyyy-MM-dd hh:mm:ss"));
            query.prepare(strQuery);

            query.exec();

            while (query.next())
            {
                DataRow data;

                data.datetime = query.value(2).toDateTime();
                data.formula = query.value(3).toString();
                data.user = query.value(4).toString();
                data.result = query.value(1).toInt();
                data.widthStd = query.value(6).toDouble();
                data.widthElStd = query.value(7).toDouble();
                data.thickness1Std = query.value(8).toDouble();
                data.thickness2Std = query.value(9).toDouble();
                data.width = query.value(10).toDouble();
                data.widthEl = query.value(11).toDouble();
                data.thickness1 = query.value(12).toDouble();
                data.thickness2 = query.value(13).toDouble();
                data.thickness3 = query.value(14).toDouble();
                data.thickness4 = query.value(15).toDouble();
                data.thickness5 = query.value(16).toDouble();

                datas.append(data);
            }

            return true;
        }

        bool Database::query(const QStringList keys, QMap<QString, QList<QPointF>>& data, const QDateTime from, const QDateTime end)
        {
            OI_F(Database);
            if (!d->_db.open())
            {
                OiWarning() << "Database::query::" << d->_db.lastError();
                return false;
            }

            QSqlQuery query(d->_db);
            QString strQuery = QString("SELECT %1 FROM %2 WHERE datetime BETWEEN '%3' AND '%4'")
                .arg("datetime," + keys.join(','))
                .arg(d->_tb)
                .arg(from.toString("yyyy-MM-dd hh:mm:ss"))
                .arg(end.toString("yyyy-MM-dd hh:mm:ss"));
            query.prepare(strQuery);
            OiWarning() << "strQuery:" << strQuery;
            query.exec();
            while (query.next())
            {
                qreal datetime = query.value(0).toDateTime().toMSecsSinceEpoch();

                int index = 1;
                for (auto& key : keys)
                {
                    data[key].append(QPointF(datetime, query.value(index).toDouble()));

                    index++;
                }
            }

            return true;
        }

        bool Database::queryFixedValues(const QStringList keys, QMap<QString, QList<QPointF>>& data, const QDateTime from, const QDateTime end)
        {
            OI_F(Database);
            if (!d->_db.open())
            {
                OiWarning() << "Database::queryFixedValues::" << d->_db.lastError();
                return false;
            }

            QSqlQuery query(d->_db);
            query.prepare(QString("SELECT %1 FROM %2 WHERE datetime BETWEEN '%3' AND '%4'")
                .arg("datetime," + keys.join(','))
                .arg(d->_tb_fixed)
                .arg(from.toString("yyyy-MM-dd hh:mm:ss"))
                .arg(end.toString("yyyy-MM-dd hh:mm:ss")));

            query.exec();
            while (query.next())
            {
                qreal datetime = query.value(0).toDateTime().toMSecsSinceEpoch();

                int index = 1;
                for (auto& key : keys)
                {
                    data[key].append(QPointF(datetime, query.value(index).toDouble()));

                    index++;
                }
            }

            return true;
        }

        QStringList Database::group(const QString key, const QString db)
        {
            OI_F(Database);

            QStringList ret;

            if(!d->_db.open())
            {
                OiWarning() << "Database::group::" << d->_db.lastError();
                return ret;
            }

            QSqlQuery query(d->_db);
            query.prepare(QString("SELECT %1 FROM %2 GROUP BY %1")
                .arg(key)
                .arg(db));

            query.exec();

            while(query.next())
            {
                auto value = query.value(0).toString();

                if(!value.isEmpty())
                {
                    ret.push_back(query.value(0).toString());
                }
            }
            return ret;
        }


        bool Database::insert(const DataRow& data)
        {
            OI_F(Database);
            if(!d->_db.open())
            {
                OiWarning() << "Database::insert::" << d->_db.lastError();
                return false;
            }

            QSqlQuery query(d->_db);

                QString queryString = QString("INSERT INTO %1 (result, datetime, formula, user, team, 总宽度标准值, P1宽度标准值, 边厚标准值, 中厚标准值, 总宽度,P1宽度,OP侧2P厚度, OP侧肩部厚度,中间厚度, DR侧肩部厚度, DR侧2P厚度) "
                    "VALUES(%2, '%3', '%4', '%5', %6, %7, %8, %9, %10, %11, %12, %13, %14, %15, %16, %17)")
                .arg(d->_tb)
                .arg(data.result)
                .arg(data.datetime.toString("yyyy-MM-dd hh:mm:ss"))
                .arg(data.formula)
                .arg(data.user)
                .arg(data.team)
                .arg(data.widthStd)
                .arg(data.widthElStd)
                .arg(data.thickness1Std)
                .arg(data.thickness2Std)
                .arg(data.width)
                .arg(data.widthEl)
                .arg(data.thickness1)
                .arg(data.thickness2)
                .arg(data.thickness3)
                .arg(data.thickness4)
                .arg(data.thickness5);
   ;

            query.prepare(queryString);
           
            bool ret = query.exec();

            if (ret)
            {
 
            }
            else
            {
                OiWarning() << query.lastError();
                OiWarning() << queryString;
            }
            QDateTime currentTime = QDateTime::currentDateTime();//系统当前时间
            currentTime = currentTime.addDays(-1);
            uint stime = currentTime.toTime_t();

            uint etime = d->_lastTime.toTime_t();
            int tRet = stime - etime;
            if (tRet > 0)
            {
                QDateTime aweekAgo = currentTime.addMonths(-2);//参数是可以为负数的，负数就是获取以前的时间，正数就是获取未来的时间
                QString deleteSql = QString("delete from '%1' where datetime < '%2'").arg(d->_tb).arg(aweekAgo.toString("yyyy-MM-dd hh:mm:ss"));
                query.exec(deleteSql);
                d->_lastTime = currentTime;
            }
            return ret;
        }

        bool Database::insert(const FixedValueRow& data)
        {
            OI_F(Database);
            if (!d->_db.open())
            {
                OiWarning() << "Database::insert FixedValueRow::" << d->_db.lastError();
                return false;
            }

            QSqlQuery query(d->_db);

            QString queryString = QString("INSERT INTO %1 (result, datetime, formula, user, team, 厚度, 厚度公差, 厚度标准值) "
                "VALUES(%2, '%3', '%4', '%5', %6, %7, %8, %9)")
                .arg(d->_tb_fixed)
                .arg(data.result)
                .arg(data.datetime.toString("yyyy-MM-dd hh:mm:ss"))
                .arg(data.formula)
                .arg(data.user)
                .arg(data.team)
                .arg(data.value)
                .arg(data.errValue)
                .arg(data.stdValue);

            query.prepare(queryString);

            bool ret = query.exec();

            if (ret)
            {

            }
            else
            {
                OiWarning() << query.lastError();
                OiWarning() << queryString;
            }

            return ret;
        }

        Database* Database::instance()
        {
            static Database s_database;
            return &s_database;
        }

    }
}